﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;

namespace DigiCardDeck
{
    public partial class List : System.Web.UI.Page
    {
        private string stConnectionString = string.Empty;
        private const string DECK_DB = "deckdb";

        protected void Page_Load(object sender, EventArgs e)
        {
            // 接続文字列を生成する
            stConnectionString += "Data Source         = " + CodeConst.DATA_SOURCE + ";";
            stConnectionString += "Initial Catalog     = " + CodeConst.INITIAL_CATALOG + ";";
            //stConnectionString += "Integrated Security = SSPI;";
            stConnectionString += "User ID             = " + CodeConst.USER_ID + ";";
            stConnectionString += "Password            = " + CodeConst.PASSWORD + ";";

            
            // SqlConnection の新しいインスタンスを生成する (接続文字列を指定)
            System.Data.SqlClient.SqlConnection cSqlConnection = (
                new System.Data.SqlClient.SqlConnection(stConnectionString)
            );

            try
            {
                // データベース接続を開く
                cSqlConnection.Open();

                // cSqlConnection から SqlCommand のインスタンスを生成する
                System.Data.SqlClient.SqlCommand hCommand = cSqlConnection.CreateCommand();

                hCommand.CommandText = "select ID,DeckNo,DeckName,Remarks from deckdb";


                //// 指定した SQL コマンドを実行する
                System.Data.SqlClient.SqlDataReader cReader = hCommand.ExecuteReader();


                DataTable table = new DataTable();
                table.TableName = "deckViewer";
                DataSet dataSet = new DataSet();

                table.Columns.Add("Id");
                table.Columns.Add("DeckNo");
                table.Columns.Add("DeckName");
                table.Columns.Add("Remarks");
                table.Columns.Add("Edit");
                table.Columns.Add("Delete");
                table.Columns.Add("Export");

                dataSet.Tables.Add(table);

                while (cReader.Read())
                {
                    //if (dataSet == null)
                    //{
                    //    Label5.Text =
                    //        "dataset is nul.";

                    //}
                    //if (dataSet.Tables.Count == 1)
                    //{
                    //    Label5.Text += "tableCount, 1.";
                    //    Label5.Text += dataSet.Tables[0].TableName;
                    //}


                    DataRow row = dataSet.Tables[0].NewRow();

                    //Label5.Text += "_newrowOK_";

                    //Label4.Text = wheXXX;

                    //if (cReader.FieldCount > 0)
                    //{
                    //    Label5.Text += "_sqlOK_";
                    //}
                    string remarks = EditRemarks(cReader);

                    row["Id"] = cReader["Id"].ToString();
                    row["DeckNo"] = cReader["DeckNo"].ToString();
                    row["DeckName"] = cReader["DeckName"].ToString();
                    row["Remarks"] = remarks;
                    row["Edit"] = "編集";
                    row["Delete"] = "削除";
                    row["Export"] = "保存";
                    dataSet.Tables[0].Rows.Add(row);

                }



                GridView1.DataSource = dataSet;
                GridView1.DataBind();
                GridView1.DataMember = dataSet.Tables[0].TableName;

                cReader.Dispose();

                hCommand.Dispose();

            }
            catch (Exception ex)
            {
                
            }
            finally
            {
                // データベース接続を閉じる
                cSqlConnection.Close();
                cSqlConnection.Dispose();

            }
        }

        private static string EditRemarks(System.Data.SqlClient.SqlDataReader cReader)
        {

            string remarks = string.Empty;
            remarks = cReader["Remarks"].ToString();
            if (remarks.Length > 15)
            {
                remarks = cReader["Remarks"].ToString().Substring(0, 15) + "...";
            }
            return remarks;
        }

        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            int index = Convert.ToInt32(e.CommandArgument);
            GridViewRow row = GridView1.Rows[index];

            if (e.CommandName.Equals("DeckDetails"))
            {
                //Label1.Text = "details";
                string value = HttpUtility.UrlEncode(row.Cells[0].Text);
                string deckNo = HttpUtility.UrlEncode(row.Cells[1].Text);
                Response.Redirect("Details.aspx?value=" + value + "&deckNo=" + deckNo);

            }
            if (e.CommandName.Equals("DeckEdit"))
            {
                //Label1.Text = "edit";
                string value = HttpUtility.UrlEncode(row.Cells[0].Text);
                string deckNo = HttpUtility.UrlEncode(row.Cells[1].Text);
                Response.Redirect("Editor.aspx?value=" + value + "&deckNo=" + deckNo);

            }
            if (e.CommandName.Equals("DeckDelete"))
            {
                //Label1.Text = "delete";
                string value = HttpUtility.UrlEncode(row.Cells[0].Text);
                string deckNo = HttpUtility.UrlEncode(row.Cells[1].Text);
                string deckName = HttpUtility.UrlEncode(row.Cells[2].Text);
                Response.Redirect("Delete.aspx?value=" + value + "&deckNo=" + deckNo + "&deckName=" + deckName);

            }
            if (e.CommandName.Equals("DeckExport"))
            {

                // ダウンロード用ファイルの名前を設定
                Response.AddHeader("Content-Disposition",
                   "attachment;filename=test.csv");
                //ダウンロードデータとして設定
                Response.ContentType = "application/octet-stream";
                //エンコードの指定
                System.Text.Encoding enc =
                   System.Text.Encoding.GetEncoding("UTF-8");

                //StringBuilder strOutdata = new StringBuilder();
                string strOutdata = string.Empty;

                // SqlConnection の新しいインスタンスを生成する (接続文字列を指定)
                System.Data.SqlClient.SqlConnection cSqlConnection = (
                    new System.Data.SqlClient.SqlConnection(stConnectionString)
                );

                try
                {
                    // データベース接続を開く
                    cSqlConnection.Open();

                    // cSqlConnection から SqlCommand のインスタンスを生成する
                    System.Data.SqlClient.SqlCommand hCommand = cSqlConnection.CreateCommand();

                    hCommand.CommandText = "select * from deckdb where ID = '" + row.Cells[0].Text + "' and  deckno = " + row.Cells[1].Text;


                    // 指定した SQL コマンドを実行する
                    System.Data.SqlClient.SqlDataReader cReader = hCommand.ExecuteReader();

                    if (!cReader.HasRows)
                    {
                        hCommand.CommandText = "select * from deckdb where ID = '" + row.Cells[0].Text + "' and  deckno = " + row.Cells[1].Text;
                        cReader.Dispose();
                        cReader = hCommand.ExecuteReader();
                    }

                    while (cReader.Read())
                    {
                        //strOutdata.Append(cReader["DeckName"].ToString() + Environment.NewLine);
                        strOutdata += cReader["DeckName"].ToString() + Environment.NewLine;
                        
                        for (int i = 1; i < 31; i++)
                        {
                            //strOutdata.Append(cReader["url" + i].ToString() + Environment.NewLine);
                            strOutdata += cReader["url" + i].ToString() + Environment.NewLine;

                        }

                    }
                }
                catch( Exception ex )
                {
                    throw;
                }


                //バイナリーで出力
                Response.BinaryWrite(enc.GetBytes(strOutdata));
                Response.End();
                strOutdata = null;
            }
        }
    }
}
